package com.mrd;


import com.mrd.util.FileUtil;
import com.mrd.util.itf.IFileContentOperation;

import org.apache.commons.lang3.StringUtils;

import java.io.FileInputStream;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数鑫泰的商品excel生成sql语句 将excel另存为text，列依次为编码（商品编码+附加码）(0)、商品名称(1)、第一单位(2)、第二单位(3)、要素(4),以制表符分隔字段，作为数据源
 * Created by winaya on 2016/11/17.
 */
public class ExecelRead {
    public static void main(String[] args) throws Exception {
        Map<String, String> units = new HashMap<>();
        List<Object> rs1 = FileUtil.getFileContent(new FileInputStream("D:\\qp\\unit.txt"), "gbk", content -> {
            String[] ss = content.split("\t");
            units.put(ss[1], ss[0]);
            return content;
        });
        FileOperation fo = new FileOperation(units);
        List<Object> rs = FileUtil.getFileContent(new FileInputStream("D:\\qp\\2017要素表.txt"), "gbk", fo);
        PrintWriter pw = new PrintWriter("D:\\qp\\goodCode.sql", "gbk");
        String tableName = "cdams_qp_goods_code";
        pw.append("prompt start import goods code...\r\n");
        pw.append("set feedback off\r\nset define off\r\n");
        pw.append("prompt Disabling triggers for ").append(tableName).append("\r\n");
        pw.append("alter table ").append(tableName).append(" disable all triggers;\r\n");
        pw.append("prompt delete the table ").append(tableName).append("'s old data").append("\r\n");
        pw.append("truncate table ").append(tableName).append(";\r\n");
        pw.append("prompt begin append new data to table ").append(tableName).append("\r\n");
        int i = 0;
        for (Object r : rs) {
            i++;
            if (i == 1) {
                continue;
            }
            Qp qp = (Qp) r;
            pw.append("\r\n").append(String.format("insert into %s(code_t,code_s,g_name,unit_1,ilow_price,goods_mode) values('%s','%s','%s','%s','%s','%s');\r\n", tableName, nullStr(qp.getCodeT()), nullStr(qp.getCodeS()), nullStr(qp.getName()).replace("'", "''").replace("\"", ""), nullStr(qp.getUnit1()), nullStr(qp.getUnit2()), nullStr(qp.getGoodMode()).replace("\"", "").replaceAll("\\d:", ":").replaceAll("\\d\\.", ":").replace(":品名;", "") + ":其他;"));
            if (i % 50 == 0) {
                pw.flush();
                pw.append("prompt finish insert ").append(Integer.toString(i)).append("....").append("\r\n");
            }
            if (i % 1000 == 0) {
                pw.append("prompt committing....").append("\r\n");
                pw.append("commit;").append("\r\n");
                pw.append("prompt committing finish").append("\r\n");
            }
        }
        pw.append("prompt finish insert ").append(Integer.toString(i)).append("....").append("\r\n");
        pw.append("prompt committing....").append("\r\n");
        pw.append("commit;").append("\r\n");
        pw.append("prompt committing finish").append("\r\n");
        pw.append("prompt Enabling triggers for ").append(tableName).append("\r\n");
        pw.append("alter table ").append(tableName).append(" enable all triggers;").append("\r\n");
        pw.append("prompt finish import goods code...").append("\r\n");
        pw.close();
    }

    private static String nullStr(String st) {
        return st == null ? "" : st;
    }
}

/**
 * 列依次为编码（商品编码+附加码）(0)、商品名称(1)、第一单位(2)、第二单位(3)、要素(4),以制表符分隔字段
 */
class FileOperation implements IFileContentOperation<Qp> {
    private int index = 0;
    private final Map<String, String> units;

    FileOperation(Map<String, String> units) {
        this.units = units;
    }


    @Override
    public Qp operationContent(String content) {
        try {
            if (index == 0) {
                index++;
                return null;
            }
            index++;
            String[] sts = content.split("\t");
            Qp qp = new Qp();
            String code = sts[0];
            qp.setCodeT(code.substring(0, 8));
            qp.setCodeS(code.substring(8));
            qp.setName(sts[1]);
            String unitCode1 = sts[2];
            if (StringUtils.isNotEmpty(unitCode1)) {
                qp.setUnit1(String.format("%03d", Integer.parseInt(unitCode1)));
            } else {
                qp.setUnit1("");
            }
//            qp.setUnit1Name(unitCode1);
            String unitCode2 = sts[3];
            if (StringUtils.isNotEmpty(unitCode2)) {
                qp.setUnit2(String.format("%03d", Integer.parseInt(unitCode2)));
            } else {
                qp.setUnit2("");
            }
            //qp.setUnit2Name(unitCode2);
            String goodMode = sts[4].replace("；", ";");
            if (!goodMode.endsWith(";")) {
                goodMode += ";";
            }
            qp.setGoodMode(goodMode);
            return qp;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(content);
        }
        return null;
    }
}

class Qp {
    private String codeT;
    private String codeS;
    private String name;
    private String unit1;
    private String unit1Name;
    private String unit2;
    private String unit2Name;
    private String goodMode;

    public String getCodeT() {
        return codeT;
    }

    public void setCodeT(String codeT) {
        this.codeT = codeT;
    }

    public String getCodeS() {
        return codeS;
    }

    public void setCodeS(String codeS) {
        this.codeS = codeS;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getUnit1() {
        return unit1;
    }

    public void setUnit1(String unit1) {
        this.unit1 = unit1;
    }

    public String getUnit2() {
        return unit2;
    }

    public void setUnit2(String unit2) {
        this.unit2 = unit2;
    }

    public String getGoodMode() {
        return goodMode;
    }

    public void setGoodMode(String goodMode) {
        this.goodMode = goodMode;
    }

    public String getUnit1Name() {
        return unit1Name;
    }

    public void setUnit1Name(String unit1Name) {
        this.unit1Name = unit1Name;
    }

    public String getUnit2Name() {
        return unit2Name;
    }

    public void setUnit2Name(String unit2Name) {
        this.unit2Name = unit2Name;
    }

    @Override
    public String toString() {
        return "Qp{" +
                "codeT='" + codeT + '\'' +
                ", codeS='" + codeS + '\'' +
                ", name='" + name + '\'' +
                ", unit1='" + unit1 + '\'' +
                ", unit1Name='" + unit1Name + '\'' +
                ", unit2='" + unit2 + '\'' +
                ", unit2Name='" + unit2Name + '\'' +
                ", goodMode='" + goodMode + '\'' +
                '}';
    }
}